﻿Imports DCM
Imports System.Data

Public Module SQL_StrDll
#Region "公共"


    Public Enum Enum_DataBaseType
        SQLServer = 0
        Oracle = 1
        SQlite = 2
        MySQL = 3
    End Enum
#End Region
#Region "单引号转义"
    Public Function SQLStr_单引号转义(str As String)
        Return str.Replace("'", "''")
    End Function
#End Region
#Region "SQLSr_Insert"


    Public Function SQLSr_InDll(str As String) As String
        Dim rs As String = str.Replace(",", "','")
        rs = "'" & rs & "'"
        Return rs
    End Function
    Public Function SQLSr_InDll(str As List(Of String)) As String
        Return SQLSr_InDll(String.Join(",", str.ToArray))
    End Function

    Public Function SQLSr_InDll(str() As String) As String
        Return SQLSr_InDll(String.Join(",", str))
    End Function
    Public Function SQLSr_InDll(str() As Integer) As String
        Return SQLSr_InDll(String.Join(",", str))
    End Function
    Public Function SQLSr_Insert(TBName As String,
                                 DR As DataRow,
                                 Optional EffectToDataBaseColumns() As String = Nothing,
                                 Optional NotEffectToDataBaseColumns() As String = Nothing,
                                 Optional OtherAddColumns() As String = Nothing,
                                 Optional OtherAddValues() As String = Nothing) As String
        If IsNothing(DR) Then Throw New Exception("请传入单行数据")
        Dim InsertColumns As New List(Of String)
        Dim InsertValues As New List(Of String)
        For i As Integer = 0 To DR.Table.Columns.Count - 1
            Dim 需要添加的标记 = True
            If IsNothing(EffectToDataBaseColumns) = False Then
                需要添加的标记 = False
                For Each item In EffectToDataBaseColumns
                    If item = DR.Table.Columns(i).ColumnName Then
                        需要添加的标记 = True
                        Exit For
                    End If
                Next
            Else
                需要添加的标记 = True
                If IsNothing(NotEffectToDataBaseColumns) = False Then
                    For Each item In NotEffectToDataBaseColumns
                        If item = DR.Table.Columns(i).ColumnName Then
                            需要添加的标记 = False
                            Exit For
                        End If
                    Next
                End If
            End If
            If 需要添加的标记 Then
                Dim ValueOB = DR(i)
                If IsNothing(ValueOB) = False Then
                    If IsDBNull(ValueOB) = False Then
                        InsertColumns.Add(DR.Table.Columns(i).ColumnName)
                        InsertValues.Add(DCM.CaseDBNullToString(ValueOB))
                    End If
                End If
            End If
        Next
        If IsNothing(OtherAddColumns) = False Or IsNothing(OtherAddValues) = False Then
            For i As Integer = 0 To OtherAddColumns.Count - 1
                InsertColumns.Add(OtherAddColumns(i))
                InsertValues.Add(OtherAddValues(i))
            Next
        End If
        Return SQLSr_Insert(TBName, InsertColumns.ToArray, InsertValues.ToArray)
    End Function

    Public Function SQLSr_Insert(Table_Name As String,
                                 InsertColumns As String,
                                 InsertValues As String) As String
        Dim RS As String = "insert into " & Table_Name
        RS = RS & "(" & InsertColumns & ")" & " values " & "(" & InsertValues & ")  "
        Return RS
    End Function
    Public Function SQLSr_Insert(Table_Name As String,
                               InsertColumns() As String,
                               InsertValues() As String,
                               Optional DataBaseType As Enum_DataBaseType = Enum_DataBaseType.SQLServer) As String
        Select Case DataBaseType
            Case Enum_DataBaseType.SQLServer
                Dim RS As String = "insert into " & Table_Name
                If InsertColumns.Length <> InsertValues.Length Then Throw New Exception("插入的列和插入的值数量不同!")
                Dim sql_OcolumnName As String = ""
                Dim sql_Values As String = ""
                For i = 0 To InsertColumns.Length - 1
                    If IsNothing(InsertValues(i)) Then
                        sql_OcolumnName = sql_OcolumnName & ",[" & InsertColumns(i) & "]"
                        sql_Values = sql_Values & "," & "null" & ""
                    Else
                        InsertValues(i) = ReplaceStr_单引号转义(InsertValues(i))
                        sql_OcolumnName = sql_OcolumnName & ",[" & InsertColumns(i) & "]"
                        If InsertValues(i) = "null" Or InsertValues(i) = "NULL" Then
                            sql_Values = sql_Values & "," & InsertValues(i) & ""
                        Else
                            sql_Values = sql_Values & ",'" & InsertValues(i) & "'"
                        End If
                    End If
                Next
                sql_OcolumnName = Mid(sql_OcolumnName, 2)
                sql_Values = Mid(sql_Values, 2)
                RS = RS & "(" & sql_OcolumnName & ")" & " values " & "(" & sql_Values & ")  "
                Return RS
            Case Enum_DataBaseType.Oracle
                Dim RS As String = "insert into " & Table_Name
                If InsertColumns.Length <> InsertValues.Length Then Throw New Exception("插入的列和插入的值数量不同!")
                Dim sql_OcolumnName As String = ""
                Dim sql_Values As String = ""
                For i = 0 To InsertColumns.Length - 1
                    If IsNothing(InsertValues(i)) Then
                        sql_OcolumnName = sql_OcolumnName & "," & InsertColumns(i) & ""
                        sql_Values = sql_Values & "," & "null" & ""
                    Else
                        If InsertValues(i).StartsWith("TO_TIMESTAMP('") = False Then
                            InsertValues(i) = ReplaceStr_单引号转义(InsertValues(i))
                        End If
                        sql_OcolumnName = sql_OcolumnName & "," & InsertColumns(i) & ""
                        If InsertValues(i) = "null" Or InsertValues(i) = "NULL" Then
                            sql_Values = sql_Values & "," & InsertValues(i) & ""
                        Else
                            If InsertValues(i).StartsWith("TO_TIMESTAMP('") Then
                                sql_Values = sql_Values & "," & InsertValues(i) & ""
                            Else
                                sql_Values = sql_Values & ",'" & InsertValues(i) & "'"
                            End If

                        End If
                    End If
                Next
                sql_OcolumnName = Mid(sql_OcolumnName, 2)
                sql_Values = Mid(sql_Values, 2)
                RS = RS & "(" & sql_OcolumnName & ")" & " values " & "(" & sql_Values & ")  "
                Return RS
            Case Else
                Throw New Exception("不支持")
        End Select

    End Function
#End Region
#Region "SQLStr_Delete"


    Public Function SQLStr_Delete(Table_Name As String,
                                 Filterstring As String) As String
        Dim ReturnSQL As String = "Delete from " & Table_Name & " where " & Filterstring
        Return ReturnSQL
    End Function
#End Region
#Region "DataTableInsertAndAdd"
    Public Function SQLStr_InserDataTableToDataBase(DT_THis As DataTable, TBName_THis As String,
                                                   Optional UnEffectColumns() As String = Nothing) As String
        Dim SQL_R As String = ""
        If IsNothing(DT_THis) Then
            Throw New Exception("数据表未实例化")
        End If
        If IsNothing(DT_THis.Rows.Count = 0) Then
            Throw New Exception("数据表是行数为0")
        End If

        For i As Integer = 0 To DT_THis.Rows.Count - 1
            Dim DR As DataRow = DT_THis.Rows(i)
            Dim sql_this As String = ""
            Dim Columns As String = ""
            Dim Values As String = ""
            For j As Integer = 0 To DT_THis.Columns.Count - 1
                Dim Flag_Skip As Boolean = False
                If IsNothing(UnEffectColumns) = False Then
                    If UnEffectColumns.Count <> 0 Then
                        For m As Integer = 0 To UnEffectColumns.Count - 1
                            If UnEffectColumns(m) = DT_THis.Columns(j).ColumnName Then
                                Flag_Skip = True
                                Exit For
                            End If
                        Next
                    End If
                End If
                If Flag_Skip = False Then
                    Columns = Columns & ",[" & DT_THis.Columns(j).ColumnName & "]"
                    If IsDBNull(DR(j)) Then
                        Values = Values & ",NULL"
                    Else
                        Dim value_Str_this = CaseDBNullToString(DR(j))
                        If value_Str_this = "" Then
                            If DT_THis.Columns(j).DataType Is GetType(String) Then
                                Values = Values & ",''"
                            Else
                                Values = Values & ",NULL"
                            End If
                        Else
                            Values = Values & ",'" & value_Str_this & "'"
                        End If
                    End If
                End If
            Next
            If Columns <> "" And Columns <> "" Then
                Columns = Mid(Columns, 2, Len(Columns) - 1) '去掉首项,
                Values = Mid(Values, 2, Len(Values) - 1)
                SQL_R += "insert into " & TBName_THis & " (" & Columns & ") values (" & Values & ")" & vbCrLf
            End If
        Next
        Return SQL_R
    End Function
    Public Function SQLtr_UpdateDataTableToDataBase(DT_THis As DataTable, TBName_THis As String,
                                                   IDName As String,
                                                   Optional UnEffectColumns() As String = Nothing) As String
        Dim SQLStr As String = ""
        If IsNothing(DT_THis) Then
            Throw New Exception("数据表未实例化")
        End If
        If IsNothing(DT_THis.Rows.Count = 0) Then
            Throw New Exception("数据表是行数为0")
        End If
        For i As Integer = 0 To DT_THis.Rows.Count - 1
            Dim DR As DataRow = DT_THis.Rows(i)
            Dim Updatestring As String = ""
            For j As Integer = 0 To DT_THis.Columns.Count - 1
                If IsNothing(UnEffectColumns) = False Then
                    Dim Flag_Skip As Boolean = False
                    If UnEffectColumns.Count <> 0 Then
                        For m As Integer = 0 To UnEffectColumns.Count - 1
                            If UnEffectColumns(m) = DT_THis.Columns(j).ColumnName Then
                                Flag_Skip = True
                                Exit For
                            End If
                        Next
                    End If
                    If IDName = DT_THis.Columns(j).ColumnName Then
                        Flag_Skip = True
                        Exit For
                    End If
                    If Flag_Skip Then Exit For
                End If

                If IsDBNull(DR(j)) Then
                    Updatestring = Updatestring & "[" & DT_THis.Columns(j).ColumnName & "]=NULL,"
                Else
                    Dim value_Str_this = CaseDBNullToString(DR(j))
                    If value_Str_this = "" Then
                        If DT_THis.Columns(j).DataType Is GetType(String) Then
                            Updatestring = Updatestring & "[" & DT_THis.Columns(j).ColumnName & "]='',"
                        Else
                            Updatestring = Updatestring & "[" & DT_THis.Columns(j).ColumnName & "]=NULL,"
                        End If
                    Else
                        Updatestring = Updatestring & "[" & DT_THis.Columns(j).ColumnName & "]='" & value_Str_this & "',"
                    End If
                End If
            Next
            If Updatestring <> "" Then
                Updatestring = DCM.StringDLL_切掉字符串_后(Updatestring, ",")
                SQLStr += " update " & TBName_THis & " set " & Updatestring & " where " & "ID" & "= " & CaseDBNullToString(DR(IDName)) & vbCrLf
            End If

        Next
        Return SQLStr
    End Function
#End Region
#Region "杂"
    Public Function SQLStr_Select(Table_Name As String,
                                  Filterstring As String,
                                  Optional TopNum As Integer = -1,
                                  Optional ColumnsNames() As String = Nothing,
                                  Optional OrderOrGroupstring As String = "",
                                  Optional DataBaseType As Enum_DataBaseType = Enum_DataBaseType.SQLServer) As String
        Dim ReturnSQL As String = ""
        Select Case DataBaseType
            Case Enum_DataBaseType.SQLServer
                Dim CS As String = "*"
                If IsNothing(ColumnsNames) = False Then
                    CS = ""
                    For i = 0 To ColumnsNames.Length - 1
                        If InStr(ColumnsNames(i).ToLower, " as ") > 0 Then
                            CS = CS & ColumnsNames(i) & ","
                        Else
                            CS = CS & "[" & ColumnsNames(i) & "],"
                        End If
                    Next
                    CS = Mid(CS, 1, Len(CS) - 1)
                End If

                If TopNum >= 0 Then
                    CS = "TOP " & TopNum & " " & CS & " "
                End If
                If IsNothing(Filterstring) Or Filterstring = "" Then
                    ReturnSQL = "select " & CS & " from " & Table_Name
                Else
                    ReturnSQL = "select " & CS & " from " & Table_Name & " where " & Filterstring
                End If
                If OrderOrGroupstring <> "" Then ReturnSQL = ReturnSQL & " " & OrderOrGroupstring
                Return ReturnSQL
            Case Enum_DataBaseType.Oracle
                Dim CS As String = "*"
                If IsNothing(ColumnsNames) = False Then
                    CS = ""
                    For i = 0 To ColumnsNames.Length - 1
                        If InStr(ColumnsNames(i).ToLower, " as ") > 0 Then
                            CS = CS & ColumnsNames(i) & ","
                        Else
                            CS = CS & "" & ColumnsNames(i) & ","
                        End If
                    Next
                    CS = Mid(CS, 1, Len(CS) - 1)
                End If
                Dim Fs1 As String = ""
                If TopNum >= 0 Then
                    Fs1 = "rownum<=" & TopNum
                End If
                If IsNothing(Filterstring) Then Filterstring = ""
                Filterstring = HeBingFilterstring_And(Fs1, Filterstring)
                If Filterstring = "" Then
                    ReturnSQL = "select " & CS & " from " & Table_Name
                Else
                    ReturnSQL = "select " & CS & " from " & Table_Name & " where " & Filterstring
                End If
                If OrderOrGroupstring <> "" Then ReturnSQL = ReturnSQL & " " & OrderOrGroupstring
                Return ReturnSQL
            Case Else
                Throw New Exception("暂时未开发该数据库的操作方式")
        End Select
    End Function
#Region "SQLSr_Update"
    Public Function SQLSr_Update(TBName As String,
                                 DR As DataRow,
                                 FilterString As String,
                                 Optional EffectToDataBaseColumns() As String = Nothing,
                                 Optional NotEffectToDataBaseColumns() As String = Nothing,
                                 Optional OtherAddColumns() As String = Nothing,
                                 Optional OtherAddValues() As String = Nothing,
                                 Optional DataBaseType As Enum_DataBaseType = Enum_DataBaseType.SQLServer) As String
        If IsNothing(DR) Then Throw New Exception("请传入单行数据")
        Dim InsertColumns As New List(Of String)
        Dim InsertValues As New List(Of String)
        For i As Integer = 0 To DR.Table.Columns.Count - 1
            Dim 需要添加的标记 = True
            If IsNothing(EffectToDataBaseColumns) = False Then
                需要添加的标记 = False
                For Each item In EffectToDataBaseColumns
                    If item = DR.Table.Columns(i).ColumnName Then
                        需要添加的标记 = True
                        Exit For
                    End If
                Next
            Else
                需要添加的标记 = True
                If IsNothing(NotEffectToDataBaseColumns) = False Then
                    For Each item In NotEffectToDataBaseColumns
                        If item = DR.Table.Columns(i).ColumnName Then
                            需要添加的标记 = False
                            Exit For
                        End If
                    Next
                End If
            End If
            If 需要添加的标记 Then
                Dim ValueOB = DR(i)
                If IsNothing(ValueOB) = False Then
                    If IsDBNull(ValueOB) = False Then
                        InsertColumns.Add(DR.Table.Columns(i).ColumnName)
                        InsertValues.Add(DCM.CaseDBNullToString(ValueOB))
                    End If
                End If
            End If
        Next
        If IsNothing(OtherAddColumns) = False Or IsNothing(OtherAddValues) = False Then
            For i As Integer = 0 To OtherAddColumns.Count - 1
                InsertColumns.Add(OtherAddColumns(i))
                InsertValues.Add(OtherAddValues(i))
            Next
        End If
        Return SQLSr_Update(TBName, InsertColumns.ToArray, InsertValues.ToArray, FilterString, DataBaseType)
    End Function

    Public Function SQLSr_Update(Table_Name As String,
                                UPdateSQL As String,
                                Filterstring As String) As String
        Dim RS As String = "update " & Table_Name & " set " & UPdateSQL
        If Filterstring <> "" Then
            RS = RS & " where " & Filterstring
        End If
        Return RS
    End Function
    Public Function SQLSr_Update(Table_Name As String,
                                UpdateColumns() As String,
                                UpdateValues() As String,
                                 Filterstring As String,
                                  Optional DataBaseType As Enum_DataBaseType = Enum_DataBaseType.SQLServer) As String
        Select Case DataBaseType
            Case Enum_DataBaseType.SQLServer
                Dim RS As String = "update " & Table_Name & " set "
                If UpdateColumns.Length <> UpdateValues.Length Then Throw New Exception("更新的列和更新的值数量不同!")
                For i As Integer = 0 To UpdateColumns.Count - 1
                    If IsNothing(UpdateValues(i)) Then
                        RS = RS & "[" & UpdateColumns(i) & "]= " & "NULL" & ","
                    Else
                        Dim ThisValue As String = ReplaceStr_单引号转义(UpdateValues(i))
                        If ThisValue.ToUpper = "NULL" Then
                            ThisValue = "NULL"
                        Else
                            ThisValue = "'" & ThisValue & "'"
                        End If
                        RS = RS & "[" & UpdateColumns(i) & "]= " & ThisValue & ","
                    End If
                Next
                RS = StringDLL_切掉字符串_后(RS, ",", True)
                If Filterstring <> "" Then
                    RS = RS & " where " & Filterstring
                End If
                Return RS
            Case Enum_DataBaseType.Oracle
                Dim RS As String = "update " & Table_Name & " set "
                If UpdateColumns.Length <> UpdateValues.Length Then Throw New Exception("更新的列和更新的值数量不同!")
                For i As Integer = 0 To UpdateColumns.Count - 1
                    If IsNothing(UpdateValues(i)) Then
                        RS = RS & "" & UpdateColumns(i) & "= " & "NULL" & ","
                    Else
                        Dim ThisValue As String = UpdateValues(i) ' ReplaceStr_单引号转义(UpdateValues(i))
                        If ThisValue.ToUpper = "NULL" Then
                            ThisValue = "NULL"
                        Else
                            If ThisValue.StartsWith("TO_TIMESTAMP(") Then

                            Else
                                ThisValue = "'" & ThisValue & "'"
                            End If

                        End If
                        RS = RS & "" & UpdateColumns(i) & "= " & ThisValue & ","
                    End If
                Next
                RS = StringDLL_切掉字符串_后(RS, ",", True)
                If Filterstring <> "" Then
                    RS = RS & " where " & Filterstring
                End If
                Return RS
            Case Else
                Throw New Exception("未开发!")
        End Select
    End Function
#End Region
    Function GetEditSQLStr(TBName_main As String) As String
        Return "select a.colid as 序号," & vbCrLf &
"a.name as 列名," & vbCrLf &
"case when b.name in('int','image','datetime','real') then b.name " & vbCrLf &
"when b.name in('decimal')then b.name + '('+convert(nvarchar(10),a.prec ) +',' + convert(nvarchar(10),isnull(a.scale,0)) +')'" & vbCrLf &
"else b.name + '('+convert(nvarchar(10),a.prec ) +')' end as 数据类型," & vbCrLf &
"d.name 主键名称, " & vbCrLf &
"d.keycnt   as 主键级别," & vbCrLf &
"case when a.isnullable=1 then '是' else '否' end as  允许null值," & vbCrLf &
"case when e.seed_value is null then null else '(' + convert(nvarchar(10),e.seed_value) +','+ convert(nvarchar(10),e.increment_value ) +')' end as 自增标识" & vbCrLf &
"from syscolumns as a" & vbCrLf &
"left outer join sysobjects as m on a.id =m.id " & vbCrLf &
"left outer join systypes  as b on a.xusertype =b .xusertype" & vbCrLf &
"left outer join sysindexkeys as c on a.id =c.id and a.colid=c.colid" & vbCrLf &
"left outer join sysindexes as d on a.id =d.id and c.indid =d.indid  " & vbCrLf &
"left outer join sys.identity_columns as e on a.id =e.object_id   and a.name =e.name" & vbCrLf &
"where m.name ='" & TBName_main & "'"
    End Function
    Function GetEditSqlStr_Oracle(TBName_main As String) As String
        Return "select c.column_id 序号, c.TABLE_NAME as 表名,c.COLUMN_NAME as 列名,c.DATA_TYPE 数据类型," & vbCrLf &
"c.DATA_LENGTH 数据长度,k.constraint_name 主键名称,k.constraint_type as 主键级别 ," & vbCrLf &
"case when c.nullable='Y' then '是' else '否' end as  允许null值, null as 自增标识" & vbCrLf &
"from USER_TAB_COLUMNS    c  " & vbCrLf &
"left outer join user_cons_columns   ck on ck.table_name=c.table_name and ck.column_name=c.column_name " & vbCrLf &
"left outer join user_constraints k on ck.constraint_name=k.constraint_name" & vbCrLf &
"where   c.table_name='" & TBName_main & "'" & vbCrLf &
"order by  c.column_id"
    End Function
#End Region
#Region "批量导入验证"
    Public Function 批量导入验证并制作批量导入表格(DT_This As DataTable, DT_Aim As DataTable) As DataTable
        Dim DT_Add As DataTable = DT_Aim.Clone
        '判断传人参数是否正确
        If DT_This.Rows.Count = 0 Then Throw New Exception("传入数据行数为0")
        '第一步,抓取目的数据表的结构
        ' Fresh_Table(TBName_This, DT_Aim, SQLConnection, , 0)
        '第二步,删除与目的数据表不一致的列
        Dim sign_haveColumn As Boolean
        For i = DT_This.Columns.Count - 1 To 0 Step -1
            sign_haveColumn = False
            For j = 0 To DT_Aim.Columns.Count - 1
                If DT_This.Columns(i).ColumnName = DT_Aim.Columns(j).ColumnName Then
                    sign_haveColumn = True
                    Exit For
                End If
            Next
            If sign_haveColumn = False Then
                DT_This.Columns.RemoveAt(i)
            End If
        Next
        DT_This.AcceptChanges()
        If DT_Aim.Columns.Count <> DT_This.Columns.Count Then
            Throw New Exception("当前数据表和目的数据表结构不同,无法载入!")
        End If
        '第三步,查看两者数据表列名是否一致
        For i = 0 To DT_Aim.Columns.Count - 1
            If DT_Aim.Columns(i).ColumnName <> DT_This.Columns(i).ColumnName Then
                Throw New Exception("当前数据列名" & DT_This.Columns(i).ColumnName & "和目的数据表列名" & DT_Aim.Columns(i).ColumnName & "不同,无法载入!")
            End If
        Next
        '第四步,将数据转移到导入表中
        For RowI As Integer = 0 To DT_This.Rows.Count - 1
            Dim Nr = DT_Add.NewRow
            For ColI As Integer = 0 To DT_Add.Columns.Count - 1
                If DT_This.Columns(ColI) Is DT_Add.Columns(ColI).DataType Then
                    Nr(ColI) = DT_This.Rows(RowI)(ColI)
                Else
                    Dim ThisStr = DCM.CaseDBNullToString(DT_This.Rows(RowI)(ColI))
                    If DT_Add.Columns(ColI).DataType Is GetType(DateTime) Then
                        Try
                            Nr(ColI) = If(ThisStr = "", DBNull.Value, CDate(ThisStr))
                        Catch ex As Exception
                            Throw New Exception("将<" & DT_Add.Columns(ColI).ColumnName & ">中的<" & ThisStr & ">转换为日期失败:" & ex.Message)
                        End Try
                    ElseIf CheckDataTypeIs数值类型(DT_Add.Columns(ColI).DataType) Then
                        Try
                            Nr(ColI) = If(ThisStr = "", DBNull.Value, Val(ThisStr))
                        Catch ex As Exception
                            Throw New Exception("将<" & DT_Add.Columns(ColI).ColumnName & ">中的<" & ThisStr & ">转换为数值失败:" & ex.Message)
                        End Try
                    Else
                        Try
                            Nr(ColI) = ThisStr
                        Catch ex As Exception
                            Throw New Exception("将<" & DT_Add.Columns(ColI).ColumnName & ">中的<" & ThisStr & ">转换为" & DT_Add.Columns(ColI).DataType.ToString & "失败:" & ex.Message)
                        End Try
                    End If
                End If
            Next
            DT_Add.Rows.Add(Nr)
        Next
        Return DT_Add
    End Function
#End Region
End Module